﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetStaffSubscriptionTeam]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetStaffSubscriptionTeam];
GO
CREATE PROCEDURE [dbo].[sproc_GetStaffSubscriptionTeam] 
    @TeamID int
/*

============================================================
功能:    得到订阅@TeamID组的人员
参数:
    @TeamID int        :    组（类）ID
============================================================

*/
AS
BEGIN
SET NOCOUNT ON

Select
		a.Staff_ID,
		a.Staff_Name,
		a.Realname,
		b.Position_Name,
		c.SubscribeDate
    From
		dbo.UDS_Staff a,
		dbo.UDS_Position b,
		dbo.UDS_Subscription c,
		dbo.UDS_Staff_In_Position d
    Where
		d.Position_id = b.Position_id 
		and a.staff_id = c.staff_id 
		and c.classid = @teamid 
		and a.dimission =0
		and a.staff_id = d.staff_id
    ORDER BY
		a.staff_name

END
GO